﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using Com.Ompchina.Lib.DBHelper;
using Model.AuthModel;
using Model.Base;
using IDAL.AuthModel;
namespace SQLServerDAL.AuthModel
{
    /// <summary>
    /// 通用组件，权限模型，数据层，管理成员
    /// </summary>
    public class StaffDAL : DalBase, IStaff
    {
        public StaffDAL() 
        {
            Schema = "am.";
            Fields = "StaffID, Username, Password, Email, IsLock, LastLogin, LastAccess, AddTime";
            TableName = "Staff";
        }

        #region IStaff 成员
        /// <summary>
        /// 新增管理员
        /// </summary>
        /// <param name="info"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public int InsertStaff(StaffInfo info, out string message)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@Username",SqlDbType.VarChar,20)
                ,new SqlParameter("@Password",SqlDbType.Char,32)
                ,new SqlParameter("@Email",SqlDbType.VarChar,100)
                ,new SqlParameter("@IsLock",SqlDbType.Char,1)
                ,new SqlParameter("@RtnID",SqlDbType.Int)
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
                ,new SqlParameter("@RtnMsg",SqlDbType.VarChar,100)
            };
            int i = 0;
            parm[i++].Value = info.Username;
            parm[i++].Value = info.Password;
            parm[i++].Value = info.Email;
            parm[i++].Value = info.IsLock;
            parm[i++].Direction = ParameterDirection.Output;
            parm[i++].Direction = ParameterDirection.Output;
            parm[i++].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure, Schema + "AddStaff", parm);
            message = parm[parm.Length - 1].Value.ToString();
            if (parm[parm.Length - 2].Value.ToString().Equals("80"))
                return int.Parse(parm[parm.Length - 3].Value.ToString());
            return 0;
        }

        /// <summary>
        /// 修改管理员
        /// </summary>
        /// <param name="info"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool UpdateStaff(StaffInfo info, out string message)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@StaffID",SqlDbType.Int)
                ,new SqlParameter("@Username",SqlDbType.VarChar,20)
                ,new SqlParameter("@Password",SqlDbType.Char,32)
                ,new SqlParameter("@Email",SqlDbType.VarChar,100)
                ,new SqlParameter("@IsLock",SqlDbType.Char,1)                
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
                ,new SqlParameter("@RtnMsg",SqlDbType.VarChar,100)
            };
            int i = 0;
            parm[i++].Value = info.StaffID;
            parm[i++].Value = info.Username;
            parm[i++].Value = info.Password;
            parm[i++].Value = info.Email;
            parm[i++].Value = info.IsLock;
            parm[i++].Direction = ParameterDirection.Output;
            parm[i++].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure, Schema + "UpdateStaff", parm);
            message = parm[parm.Length - 1].Value.ToString();
            return parm[parm.Length - 2].Value.ToString().Equals("80");
        }

        /// <summary>
        /// 删除成员
        /// </summary>
        /// <param name="staffID"></param>
        /// <returns></returns>
        public bool DeleteStaff(int staffID)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@StaffID",SqlDbType.Int)
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
            };
            int i = 0;
            parm[i++].Value = staffID;
            parm[i++].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure, Schema + "DeleteStaff", parm);
            return parm[parm.Length - 1].Value.ToString().Equals("80");
        }

        /// <summary>
        /// 根据编号获取管理员对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public StaffInfo GetStaffByID(int id)
        {
            Sql = string.Format("SELECT {0} FROM {1} WHERE StaffID = {2}", Fields, Schema + TableName, id);
            SqlDataReader dr = SqlHelper.ExecuteReader(base.ConnectionString, CommandType.Text, Sql);
            StaffInfo info = new StaffInfo();
            if (dr != null)
            {
                if (dr.Read())
                {
                    int i = 0;
                    info.StaffID = dr.GetInt32(i++);
                    info.Username = dr.GetString(i++);
                    info.Password = dr.GetString(i++);
                    info.Email = dr.GetString(i++);
                    info.IsLock = dr.GetString(i++);
                    info.LastLogin = dr.GetDateTime(i++);
                    info.LastAccess = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                }
                dr.Close();
            }
            return info;
        }

        /// <summary>
        /// 根据管理员名称获取管理员对象
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public StaffInfo GetStaffByName(string username)
        {
            Sql = string.Format("SELECT {0} FROM {1} WHERE Username = '{2}'", Fields, Schema + TableName, username);
            SqlDataReader dr = SqlHelper.ExecuteReader(base.ConnectionString, CommandType.Text, Sql);
            StaffInfo info = new StaffInfo();
            if (dr != null)
            {
                if (dr.Read())
                {
                    int i = 0;
                    info.StaffID = dr.GetInt32(i++);
                    info.Username = dr.GetString(i++);
                    info.Password = dr.GetString(i++);
                    info.Email = dr.GetString(i++);
                    info.IsLock = dr.GetString(i++);
                    info.LastLogin = dr.GetDateTime(i++);
                    info.LastAccess = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                }
                dr.Close();
            }
            return info;
        }

        public IList<StaffInfo> ListStaff(BaseCondition condition, out int recordCount)
        {
            Wheres = new System.Text.StringBuilder(" 1 = 1 ");
            recordCount = base.PageListCounter(Schema + TableName, Wheres.ToString());
            SqlDataReader dr = base.PageListRecord(Fields, Schema + TableName, Wheres.ToString()
                , condition.OrderBy, condition.PageIndex, condition.PageSize);
            List<StaffInfo> list = new List<StaffInfo>();
            if (dr != null)
            {
                while (dr.Read())
                {
                    StaffInfo info = new StaffInfo();
                    int i = 0;
                    info.StaffID = dr.GetInt32(i++);
                    info.Username = dr.GetString(i++);
                    info.Password = dr.GetString(i++);
                    info.Email = dr.GetString(i++);
                    info.IsLock = dr.GetString(i++);
                    info.LastLogin = dr.GetDateTime(i++);
                    info.LastAccess = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                    list.Add(info);
                }
                dr.Close();
            }
            return list;
        }
        #endregion
    }
}
